﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.Linq;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text.RegularExpressions;
using System.Web;
using System.Web.Caching;

namespace Core
{
    /// <summary>
    /// This class represent Extensions of LINQ
    /// </summary>
    public static class LINQExtensions
    {
        
        /// <summary>
        /// Deletes all.
        /// </summary>
        /// <param name="dc">The dc.</param>
        /// <param name="collection">The collection.</param>
        /// <returns></returns>
        public static int DeleteAll(this DataContext dc, IQueryable collection)
        {
            DbCommand comm = dc.GetCommand(collection);
            Regex selectRE = new Regex("^SELECT[\\s]*(?<Fields>.*)[\\s]*FROM[\\s]*(?<Table>.*)[\\s]*AS[\\s]*(?<TableAlias>.*)[\\s]*WHERE[\\s]*(?<Condition>.*)", RegexOptions.IgnoreCase);
            //Regex selectRE = new Regex("^SELECT[\\s]*(?.*)[\\s]*FROM[\\s]*(? .*)[\\s]*AS[\\s]*(?.*)[\\s]*WHERE[\\s]*(?.*)", RegexOptions.IgnoreCase);
            Match m = selectRE.Match(comm.CommandText);

            if (!m.Success)
                throw new ArgumentException("Cannot delete this type of collection");

            string table = m.Groups["Table"].Value.Trim();
            string tableAlias = m.Groups["TableAlias"].Value.Trim();
            string condition = m.Groups["Condition"].Value.Trim().Replace(tableAlias, table);

            comm.CommandText = string.Format("DELETE FROM {0} WHERE {1}", table, condition);

            if (comm.Connection.State != ConnectionState.Open)
                comm.Connection.Open();

            return comm.ExecuteNonQuery();

        }

        /// <summary>
        /// Toes the ADO table.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <returns></returns>
        public static DataTable ToADOTable(this IQueryable query)
        {
            DataTable dtReturn = new DataTable();
            PropertyInfo[] columnProperties = null;

            foreach (var record in query)
            {
                // use reflection to get column names for the table, only first time, others will follow
                if (columnProperties == null)
                {
                    columnProperties = record.GetType().GetProperties();
                    foreach (PropertyInfo propertyInfo in columnProperties)
                    {
                        // sort out the issue of nullable types
                        Type columnType = propertyInfo.PropertyType;
                        if ((columnType.IsGenericType) && (columnType.GetGenericTypeDefinition() == typeof(Nullable<>)))
                        {
                            columnType = columnType.GetGenericArguments()[0];
                        }

                        // add a column to the table
                        dtReturn.Columns.Add(new DataColumn(propertyInfo.Name, columnType));
                    }
                }

                DataRow dataRow = dtReturn.NewRow();

                foreach (PropertyInfo propertyInfo in columnProperties)
                {
                    dataRow[propertyInfo.Name] = propertyInfo.GetValue(record, null) == null ? DBNull.Value : propertyInfo.GetValue(record, null);

                    if (dataRow[propertyInfo.Name].ToString().Contains("System.Data.Linq.EntitySet"))
                    {
                        throw new Exception("Please select column before call LINQExtensions.ToADOTable method.");
                    }
                }

                dtReturn.Rows.Add(dataRow);
            }

            return dtReturn;
        }

        /// <summary>
        /// LINQs to data table.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="varlist">The varlist.</param>
        /// <returns></returns>
        public static DataTable LINQToDataTable<T>(IEnumerable<T> varlist)
        {
            DataTable dtReturn = new DataTable();

            // column names 
            PropertyInfo[] oProps = null;

            if (varlist == null) return dtReturn;

            foreach (T rec in varlist)
            {
                // Use reflection to get property names, to create table, Only first time, others will follow 
                if (oProps == null)
                {
                    oProps = rec.GetType().GetProperties();
                    foreach (PropertyInfo pi in oProps)
                    {
                        Type colType = pi.PropertyType;

                        if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition()
                        == typeof(Nullable<>)))
                        {
                            colType = colType.GetGenericArguments()[0];
                        }

                        dtReturn.Columns.Add(new DataColumn(pi.Name, colType));
                    }
                }

                DataRow dr = dtReturn.NewRow();

                foreach (PropertyInfo pi in oProps)
                {
                    dr[pi.Name] = pi.GetValue(rec, null) == null ? DBNull.Value : pi.GetValue
                    (rec, null);
                }

                dtReturn.Rows.Add(dr);
            }
            return dtReturn;
        }

        /// <summary>
        /// LINQs the cache.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="query">The query.</param>
        /// <returns></returns>
        public static List<T> LINQCache<T>(this Table<T> query) where T : class
        {
            string tableName = query.Context.Mapping.GetTable(typeof(T)).TableName;
            List<T> result = HttpContext.Current.Cache[tableName] as List<T>;

            if (result == null)
            {
                using (SqlConnection cn = new SqlConnection(query.Context.Connection.ConnectionString))
                {
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(query.Context.GetCommand(query).CommandText, cn);
                    cmd.Notification = null;
                    cmd.NotificationAutoEnlist = true;

                    SqlCacheDependencyAdmin.EnableNotifications(GlobalUtility.KcDbConnectionString);
                    if (!SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(GlobalUtility.KcDbConnectionString).Contains(tableName))
                    {
                        SqlCacheDependencyAdmin.EnableTableForNotifications(GlobalUtility.KcDbConnectionString, tableName);
                    }

                    SqlCacheDependency dependency = new SqlCacheDependency(cmd);
                    cmd.ExecuteNonQuery();
                    HttpContext.Current.Cache.Insert(tableName, query.ToList(), dependency);

                    result = query.ToList();
                }
            }
            return result;
        }
    }
}
